#!/usr/bin/env python
# -*- coding: UTF-8 -*-
#
# Copyright [Gtlions Lai].
# Create Date:
# Update Date:
__authors__ = '"Gtlions Lai" <gtlions.l@qq.com>'
"""Function for GreenPlum DB tables partitions manager - add partitions.

Add tables partitions.

  ClassFoo: None.
  functionBar(): None.
"""

import sys
import os
import platform
import colorama
import time

if platform.system() == 'Windows' or platform.system() == 'Darwin':
    from psycopg2 import *
elif platform.system() == 'Linux':
    from pygresql.pgdb import *
import datetime


class cmccgp_check:
    """Function for GreenPlum DB status check.
    It's usage only on CMCC-FUJIAN.

    Usage: cmccgp_check
    """
    """
    Function List:
        partition()
        session()
        space()
    """

    def __init__(self, host, database, user, password):
        self.host = host
        self.database = database
        self.user = user
        self.password = password
        try:
            self.db = connect(host=self.host, database=self.database, user=self.user, password=self.password)
            self.cur = self.db.cursor()
        except InternalError, e:
            print 'ERROR: could not connect to server: ' + self.host + ',' + self.database + ',' + self.user

    def dbid(self):
        # print time.strftime('%Y%m%d %H:%M:%S',time.localtime(time.time()))
        self.sql = "select current_database()"
        self.cur.execute(self.sql)
        return 'Current Database is :[ ' + self.cur.fetchone()[0] + ' ]'

    def partition(self, level):
        self.sql = "select 序号,表归属模式,表名称,case when 理论当前最小分区>当前最小分区 then '最小分区异常' when   当前最大分区<理论当前最大分区 then '最大分区异常' end 异常情况 from (select b.add_result,b.drop_result,b.id 序号,case when b.retention_m is null then '日表' else '月表' end 分区类型,a.schemaname 表归属模式,b.tableowner 表拥有者,a.tablename 表名称,coalesce(b.retention_m||'_Months',b.retention_d||'_Days',b.retention_h||'_Days') 保留时间,case when b.retention_d is not null or b.retention_h is not null then to_char(current_date-(coalesce(b.retention_d,b.retention_h))+1,'yyyymmdd') when b.retention_m is not null then to_char(date_trunc('months',oracompat.add_months(current_date,-b.retention_m)),'yyyymm') end 理论当前最小分区,substr(regexp_replace(a.minprt,'[^0-9]','','g'),1,8) 当前最小分区,coalesce(b.reserve_m,b.reserve_d,b.reserve_h) 预留天数,substr(regexp_replace(a.maxprt,'[^0-9]','','g') ,1,8)当前最大分区,case when b.retention_d is not null or b.retention_h is not null then to_char(current_date+coalesce(b.reserve_d,b.reserve_h)-1,'yyyymmdd') else to_char(date_trunc('months',oracompat.add_months(current_date,b.reserve_m-1)),'yyyymm') end 理论当前最大分区 from (select a.schemaname,a.tablename,min(case when a.partitionrank=1 then a.partitionname else null end) minprt,max(a.partitionname) maxprt from pg_partitions a where a.partitionlevel=0  group by a.schemaname,a.tablename) a,gpmg.manager_table b where a.tablename=b.tablename and a.schemaname=b.schemaname and (b.status=1 or (status=0 and expire>current_date)) order by b.id) a where 理论当前最小分区>当前最小分区 or 当前最大分区<理论当前最大分区 order by 序号"
        self.cur.execute(self.sql)
        result = self.cur.fetchall()
        dbinfo = self.dbid()
        if level == 'w':
            if result:
                print '----------分区信息' + dbinfo + '----------'
                for partition in result:
                    print partition[0], '	', partition[1], '	', partition[2], '	', partition[3]
        elif level == 'l':
            print '----------分区信息' + dbinfo + '----------'
            for partition in result:
                print partition[0], '	', partition[1], '	', partition[2], '	', partition[3]

    def session(self, level):
        self.sql = "select datname,elapsed,waiting,procpid from (select 'select pg_cancel_backend('||procpid||');' Cancel_Sql,\
	'select pg_terminate_backend('||procpid||');' Termiante_Sql,\
	datname,procpid,sess_id,usename,\
	current_query,waiting,age(now(),query_start) elapsed,\
	query_start,backend_start,client_addr,application_name,xact_start \
	from pg_stat_activity where current_query!='<IDLE>' \
order by elapsed desc) a where elapsed > interval '59 min' order by 2 desc limit 5"
        self.cur.execute(self.sql)
        result = self.cur.fetchall()
        dbinfo = self.dbid()
        if level == 'w':
            if result:
                print '----------会话信息' + dbinfo + '----------'
                for session in result:
                    print session
        elif level == 'l':
            print '----------会话信息' + dbinfo + '----------'
            for session in result:
                print session

    def space(self, level):
        self.sql = """select * from (select 'total' type,replace(dfusage,'%','') dfusage from gpmg.disk_usage where id =(select max(id) from gpmg.disk_usage where dfhostname like 'TotalSegHosts%') and dfhostname like 'TotalSegHosts%'
			union all
			select 'segs',max(replace(dfusage,'%','')) dfusage from gpmg.disk_usage where id =(select max(id) from gpmg.disk_usage where dfhostname like 'TotalSegHosts%')) a where dfusage>75"""
        self.cur.execute(self.sql)
        result = self.cur.fetchall()
        dbinfo = self.dbid()
        if level == 'w':
            if result:
                print '----------空间信息' + dbinfo + '----------'
                for space in result:
                    print space
        elif level == 'l':
            print '----------空间信息' + dbinfo + '----------'
            for space in result:
                print space

    def perf(self, level):
        self.sql = """select round(avg(cpu_sys)::numeric,2) cpu_sys,round(avg(cpu_user)::numeric,2) cpu_user,round(avg(mem_total/1024/1024.0/1024)::numeric,2) mem_total,round(avg(mem_used/1024/1024.0/1024)::numeric,2) mem_used,round(avg(disk_rb_rate/1024/1024.0)::numeric,2)+round(avg(disk_wb_rate/1024/1024.0)::numeric,2) disk_rb,round(avg(disk_rb_rate/1024/1024.0)::numeric,2) disk_rb_rate,round(avg(disk_wb_rate/1024/1024.0)::numeric,2) disk_wb_rate,round(avg(disk_ro_rate)::numeric,2)+round(avg(disk_wo_rate)::numeric,2) disk_ro,round(avg(disk_ro_rate)::numeric,2) disk_ro_rate,round(avg(disk_wo_rate)::numeric,2) disk_wo_rate from system_history where ctime>=now()-interval '15 minutes' and hostname not in ('mdw','smdw')"""
        self.cur.execute(self.sql)
        result = self.cur.fetchall()
        dbinfo = self.dbid()
        if level == 'w':
            if result:
                for perf in result:
                    if perf[0] + perf[1] > 90:
                        print '----------性能信息' + dbinfo + '----------'
                        print perf
        elif level == 'l':
            print '----------性能信息' + dbinfo + '----------'
            for perf in result:
                print perf


if __name__ == "__main__":
    prodbinfo = {
        'bomc': ['10.46.155.189', 'loglapdb', 'gpadmin', 'gpadminBOMC123$'],
        'bi': ['10.46.219.48', 'bigdatagp', 'gpadmin', 'gpadminBI123$'],
        'boss': ['10.46.218.29', 'ocsgpdb', 'gpadmin', 'gpadminBOSS123$']
    }
    perfdbinfo = {
        'bomc': ['10.46.155.189', 'gpperfmon', 'gpadmin', 'gpadminBOMC123$'],
        'bi': ['10.46.219.48', 'gpperfmon', 'gpadmin', 'gpadminBI123$'],
        'boss': ['10.46.218.29', 'gpperfmon', 'gpadmin', 'gpadminBOSS123$']
    }
    if sys.argv[1] == 'l':
        dbClassList = []
        for dbinfokey in prodbinfo.keys():
            dbClassList.append(cmccgp_check(prodbinfo[dbinfokey][0], prodbinfo[dbinfokey][1], prodbinfo[dbinfokey][2],
                                            prodbinfo[dbinfokey][3]))
        for db in dbClassList:
            db.dbid()
            db.partition('l')
            db.session('l')
            db.space('l')
        dbClassList = []
        for dbinfokey in perfdbinfo.keys():
            dbClassList.append(
                cmccgp_check(perfdbinfo[dbinfokey][0], perfdbinfo[dbinfokey][1], perfdbinfo[dbinfokey][2],
                             perfdbinfo[dbinfokey][3]))
        for db in dbClassList:
            db.dbid()
            db.perf('l')
    elif sys.argv[1] == 'w':
        dbClassList = []
        for dbinfokey in prodbinfo.keys():
            dbClassList.append(cmccgp_check(prodbinfo[dbinfokey][0], prodbinfo[dbinfokey][1], prodbinfo[dbinfokey][2],
                                            prodbinfo[dbinfokey][3]))
        for db in dbClassList:
            db.dbid()
            db.partition('w')
            db.session('w')
            db.space('w')
        dbClassList = []
        for dbinfokey in perfdbinfo.keys():
            dbClassList.append(
                cmccgp_check(perfdbinfo[dbinfokey][0], perfdbinfo[dbinfokey][1], perfdbinfo[dbinfokey][2],
                             perfdbinfo[dbinfokey][3]))
        for db in dbClassList:
            db.dbid()
            db.perf('w')
